
package com.contactmodule.dao;
import com.companymodule.bean.TbCompany;
import com.companymodule.bean.TbCompanyDepartMiddle;
import com.companymodule.dao.TbCompanyDAO;
import com.companymodule.dao.TbCompanyDepartMiddleDAO;
import com.contactmodule.bean.*;

import java.sql.*;
import java.io.*;
import java.util.*;

import util.*;
public class TbContactDAO {
    private Connection conn = null;
    private PreparedStatement pt = null;
    private ResultSet rs = null;
    private boolean flag = false;
    private String sql = "";
    private TbContact tbContact=null;
    private List<TbContact> tbContactList=null;

    public boolean add(TbContact inTbContact){
        try{
            
        conn=ConnectDB.getSqlServerConnection();
        sql="insert into Tb_Contact(ContactName,DepartId,ContactSex,ContactPhone,ContactEmail,ContactBirthday,ContactAddress,ContactState,UserId,CompanyId) values(?,?,?,?,?,?,?,?,?,?)";
        System.out.println("companyId=="+inTbContact.getCompanyId());
        pt = conn.prepareStatement(sql);
        pt.setString(1,inTbContact.getContactName());
        pt.setInt(2,inTbContact.getDepartId());
        pt.setString(3,inTbContact.getContactSex());
        pt.setString(4,inTbContact.getContactPhone());
        pt.setString(5,inTbContact.getContactEmail());
        pt.setString(6,inTbContact.getContactBirthday());
        pt.setString(7,inTbContact.getContactAddress());
        pt.setInt(8,inTbContact.getContactState());
        pt.setInt(9,inTbContact.getUserId());
        pt.setInt(10,inTbContact.getCompanyId());
        if (pt.executeUpdate() > 0) flag=true;
        }catch(Exception e){
            e.printStackTrace();
        }finally {
                        ConnectDB.closeSqlConnection();        }
        return flag;
    }
    public boolean del(int inContactId){
        return UtilDAO.del("Tb_Contact", "ContactId", inContactId);
    }
    public boolean edit(TbContact inTbContact){
        try{
            
        conn=ConnectDB.getSqlServerConnection();
        sql="update Tb_Contact set ContactName=?,ContactSex=?,ContactPhone=?,ContactEmail=?,ContactBirthday=?,ContactAddress=?,ContactState=?,UserId=?,CreateTime=getdate() where ContactId=?";
        pt = conn.prepareStatement(sql);
        
        pt.setString(1,inTbContact.getContactName());
        pt.setString(2,inTbContact.getContactSex());
        pt.setString(3,inTbContact.getContactPhone());
        pt.setString(4,inTbContact.getContactEmail());
        pt.setString(5,inTbContact.getContactBirthday());
        pt.setString(6,inTbContact.getContactAddress());
        pt.setInt(7,inTbContact.getContactState());
        pt.setInt(8,inTbContact.getUserId());
        pt.setInt(9,inTbContact.getContactId());
        if (pt.executeUpdate() > 0) flag=true;
        }catch(Exception e){
            e.printStackTrace();
        }finally {
                        ConnectDB.closeSqlConnection();        }
        return flag;
    }
    public TbContact getTbContactByContactId(int inContactId) {
        tbContact=null;
        try{
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from Tb_Contact where ContactId=? ";
            pt = conn.prepareStatement(sql);
            pt.setInt(1,inContactId);
            rs = pt.executeQuery();
            while (rs.next()) {
                if (tbContact == null)tbContact = getTbContact(rs);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
                        ConnectDB.closeSqlConnection();        }
        return tbContact;
    }
    public List<TbContact> getTbContactAll() {
        tbContactList=new ArrayList<TbContact>();
        try{
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from Tb_Contact";
            pt = conn.prepareStatement(sql);
            rs = pt.executeQuery();
            while (rs.next()) {
                tbContactList.add(getTbContact(rs));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
                        ConnectDB.closeSqlConnection();        }
        return tbContactList;
    }
    public List<TbContact> getTbContactAll(VskPage vp) {
        tbContactList=new ArrayList<TbContact>();
        try{
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from Tb_Contact";
            vp.setAllCountSql(sql);
            vp.getAllRecordCount(conn, vp.getAllCountSql());
            pt = conn.prepareStatement(vp.processPageSql( "Tb_Contact", "ContactId"));
            rs = pt.executeQuery();
            while (rs.next()) {
                tbContactList.add(getTbContact(rs));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
                        ConnectDB.closeSqlConnection();        }
        return tbContactList;
    }
    public TbContact getTbContact(ResultSet rs) throws Exception{
        TbContact tbContact = new TbContact();
        tbContact.setContactId(rs.getInt("ContactId"));
        tbContact.setContactName(rs.getString("ContactName"));
        tbContact.setCompanyId(rs.getInt("CompanyId"));
        tbContact.setDepartId(rs.getInt("DepartId"));
        tbContact.setContactSex(rs.getString("ContactSex"));
        tbContact.setContactPhone(rs.getString("ContactPhone"));
        tbContact.setContactEmail(rs.getString("ContactEmail"));
        tbContact.setContactBirthday(rs.getString("ContactBirthday"));
        tbContact.setContactAddress(rs.getString("ContactAddress"));
        tbContact.setContactState(rs.getInt("ContactState"));
        tbContact.setUserId(rs.getInt("UserId"));
        tbContact.setCreateTime(rs.getString("CreateTime"));
        return tbContact;
    }
    /**
     * 根据条件得到公司或某个部门下的员工
     *@author qingyu zhang
     *@function:
     * @param condition
     * @return
     *2011-6-16
     *下午07:01:29
     *ProjectManagerT
     *com.contactmodule.dao
     *List<TbContact>
     */
    public List<TbContact> getContactList(String condition){
    	try {
			conn = ConnectDB.getSqlServerConnection();
			sql="select * from Tb_Contact where "+condition;
			System.out.println("sql-----"+sql);
			pt = conn.prepareStatement(sql);
			rs = pt.executeQuery();
			while(rs.next()){
				if(tbContactList==null)tbContactList= new ArrayList<TbContact>();
				tbContactList.add(this.getTbContact(rs));
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			 ConnectDB.closeSqlConnection();  
		}
		return tbContactList;
    }
    
    public String getLastResult(){
    	long start =System.currentTimeMillis();
    	List<TbDepart> firstDepartList = new TbDepartDAO().getDepartListByParId(0);//get the first depat
    	Hashtable<TbCompany,List<TbDepart>> ht = new Hashtable<TbCompany,List<TbDepart>>();
    	
    	List<TbCompany> companyList = new TbCompanyDAO().getTbCompanyAll();
    	for(TbCompany company:companyList){
    		List<TbDepart> fatherDepart = new ArrayList<TbDepart>();
    		List<TbCompanyDepartMiddle> companyDepartList  = new TbCompanyDepartMiddleDAO().getCompanyDepartList(company.getCompanyId());
    		if(companyDepartList!=null&&companyDepartList.size()>0){
    			for(TbCompanyDepartMiddle companyDepartMiddle:companyDepartList){
    				TbDepart depart = new TbDepartDAO().getTbDepartByDepartId(companyDepartMiddle.getDepartId());
    				if(depart.getParentDepartId()==0){
    					fatherDepart.add(depart);
    				}
    			}
    			ht.put(company, fatherDepart);
    		}else{
    			ht.put(company, new ArrayList<TbDepart>());
    		}
    	}
    	long end1 =System.currentTimeMillis();
    	String result="";
    	if(ht.size()>0){
    	   Iterator<TbCompany> it = 	ht.keySet().iterator();
    	   for(TbCompany company:companyList){
    	   //while(it.hasNext()){
    		  // TbCompany company = it.next();
    		   result+="data['1_C"+company.getCompanyId()+"90000'] = 'text: "+company.getCompanyName()+"; url:../TbContactServlet?method=contactListById&id="+company.getCompanyId()+"&typeId=0';";
    		     List<TbDepart> depList= (ArrayList<TbDepart>)ht.get(company);//get the first level depart
    		   if(depList!=null&&depList.size()>0){
    			   for(TbDepart dep:depList){
    				   result+="data['C"+company.getCompanyId()+"90000_D"+dep.getDepartId()+"80000'] = 'text: "+dep.getDepartName()+"; url:../TbContactServlet?method=contactListById&id="+dep.getDepartId()+"&typeId=1';";
        			   StringBuffer sb = this.getResult(null, dep.getDepartId());
        			   int sbLength = sb.length();
        			   if(sbLength>0){
        				   result+=sb.toString();
        			   }
    			   }
    			  
    		   }
    	   }
    	}
    	return result;
    }
    public StringBuffer getResult(StringBuffer sb ,int departId){
    	StringBuffer result = null;
    	if(sb==null){
    		result= new StringBuffer();
    	}else{
    		result=sb;
    	}
    	List<TbDepart> departList = new TbDepartDAO().getDepartListByParId(departId);
    	if(departList!=null&&departList.size()>0){
    		for(TbDepart tbDepart:departList){
    			result.append("data['D"+departId+"80000_D"+tbDepart.getDepartId()+"80000'] = 'text: "+tbDepart.getDepartName()+"; url:../TbContactServlet?method=contactListById&id="+tbDepart.getDepartId()+"&typeId=1';");
    			this.getResult(result, tbDepart.getDepartId());
    		}
    	}
    	return result;
    }
}
